﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PBOffice.Model;
using System.Data;
using System.Data.SqlClient;

namespace PBOffice.DAL
{
    public class BranchServcies
    {
        /// <summary>
        /// 获取全部机构的信息
        /// </summary>
        /// <returns>返回全部机构信息</returns>
        public List<Branch> GetAllBranch()
        {
            string sql = "select * from Branch ";
            List<Branch> braList = new List<Branch>();
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    Branch bra = new Branch();
                    bra.BranchId = (int)dr["BranchId"];
                    bra.BranchName = dr["BranchName"].ToString();
                    bra.BranchShortName = dr["BranchShortName"].ToString();
                    bra.PrincipalUser = dr["PrincipalUser"].ToString();
                    bra.ConnectTelNo = dr["ConnectTelNo"].ToString();
                    bra.ConnectMobileTelNo = dr["ConnectMobileTelNo"].ToString();
                    bra.Faxes = dr["Faxes"].ToString();
                    braList.Add(bra);
                }
                return braList;
            }

        }
        /// <summary>
        /// 向机构中添加一条信息
        /// </summary>
        /// <param name="bra"></param>
        /// <returns>是否添加成功</returns>
        public bool AddBranch(Branch bra)
        {
            String sql = "insert into Branch(BranchName,BranchShortName,PrincipalUser,ConnectTelNo,ConnectMobileTelNo,Faxes) values (@BranchName,@BranchShortName,@PrincipalUser,@ConnectTelNo,@ConnectMobileTelNo,@Faxes)";
            SqlParameter[] paras =
            {
                new SqlParameter("@BranchName",bra.BranchName),
                new SqlParameter("@BranchShortName",bra.BranchShortName),
                new SqlParameter("@PrincipalUser",bra.PrincipalUser),
                new SqlParameter("@ConnectTelNo",bra.ConnectTelNo),
                new SqlParameter("@ConnectMobileTelNo",bra.ConnectMobileTelNo),
                new SqlParameter("@Faxes",bra.Faxes),
            };
            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, sql, paras) > 0;
        }
        /// <summary>
        /// 根据机构名称获取机构信息
        /// </summary>
        /// <param name="BranchName"></param>
        /// <returns></returns>
        public List<Branch> GetSingleBranch(string BranchName)
        {
            List<Branch> braList = new List<Branch>();
            string sql = "select * from Branch where BranchName=@BranchName";
            SqlParameter[] paras =
            {
                new SqlParameter("@BranchName",BranchName),
            };
            using (IDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, sql, paras))
            {
                while (dr.Read())
                {
                    Branch bra = new Branch();
                    bra.BranchId = (int)dr["BranchId"];
                    bra.BranchName = dr["BranchName"].ToString();
                    bra.BranchShortName = dr["BranchShortName"].ToString();
                    bra.PrincipalUser = dr["PrincipalUser"].ToString();
                    bra.ConnectTelNo = dr["ConnectTelNo"].ToString();
                    bra.ConnectMobileTelNo = dr["ConnectMobileTelNo"].ToString();
                    bra.Faxes = dr["Faxes"].ToString();
                    braList.Add(bra);
                }
                return braList; 
            }
 
        }
        /// <summary>
        /// 更新GridView中的信息
        /// </summary>
        /// <param name="branch"></param>
        /// <returns></returns>
        public bool UpdateBranch(Branch branch)
        {
            string sql = "UPDATE Branch SET BranchName=@BranchName,BranchShortName=@BranchShortName,PrincipalUser=@PrincipalUser,ConnectTelNo=@ConnectTelNo,ConnectMobileTelNo=@ConnectMobileTelNo,Faxes=@Faxes WHERE BranchId=@BranchId";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@BranchId", branch.BranchId),
                new SqlParameter("@BranchName", branch.BranchName),
                new SqlParameter("@BranchShortName", branch.BranchShortName),
				new SqlParameter("@ConnectMobileTelNo", branch.ConnectMobileTelNo),
				new SqlParameter("@Faxes", branch.Faxes),
				new SqlParameter("@PrincipalUser", branch.PrincipalUser),
				new SqlParameter("@ConnectTelNo", branch.ConnectTelNo)
			};
            bool result = (int)SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, sql, paras)>0;
            return result;
        }
        /// <summary>
        /// 删除指定ID的机构信息
        /// </summary>
        /// <param name="BranchId"></param>
        /// <returns></returns>
        public bool DeleteBranch(int BranchId)
        {
            string sql = "DELETE FROM Branch WHERE BranchId=" + BranchId;
            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, sql) > 0;
        }
        
    }
}
